package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import beans.Account;
import beans.Database;

public class AccountDAO {

	public static boolean update(Account account) {
		Connection conn = null;
		PreparedStatement pre = null;
		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("UPDATE member SET username=?, displayname=?, type=?, m_state=? WHERE id=?");
			pre.setString(1, account.getUsername());
			pre.setString(2, account.getDisplayName());
			pre.setString(3, account.getType());
			pre.setInt(4, account.getState());
			pre.setInt(5, account.getId());

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(Account account) {
		return false;
	}

	/**
	 * use select account in thread and comment
	 * 
	 * @param id
	 * @return
	 */
	public static Account selectSumaryById(int id) {
		Account account = null;
		try {
			Connection conn = Database.connect();
			PreparedStatement pre = conn
					.prepareStatement("SELECT member.username, member.displayname, member.type, member.avatar, member.m_state, member.posts FROM member WHERE id=?");
			pre.setInt(1, id);
			ResultSet rs = pre.executeQuery();

			while (rs.next()) {
				account = new Account(id, rs.getString("username"),
						rs.getString("displayname"), rs.getString("type"),
						rs.getString("avatar"), rs.getInt("m_state"),
						rs.getInt("posts"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return account;
	}

	public static Account selectFullById(int id) {
		Account account = null;
		String query = "SELECT *,(SELECT count(*) FROM comment WHERE comment.user_id=member.id) + (SELECT count(*) FROM thread WHERE user_id=member.id) AS posts FROM member WHERE id=?";
		ResultSet rs = executeQuery(query, id + "");
		try {
			if (rs.next()) {
				account = new Account(rs.getInt("id"),
						rs.getString("username"), rs.getString("displayname"),
						rs.getString("type"), rs.getInt("m_state"));
				account.setAvatar(rs.getString("avatar"));
				account.setBirthday(rs.getDate("birthday"));
				account.setJoin(rs.getDate("join"));
				account.setNumPosts(rs.getInt("posts"));
				account.setAddresss(rs.getString("address"));
				account.setJob(rs.getString("job"));
				account.setEmail(rs.getString("email"));
				account.setSex(rs.getString("sex"));
				account.setSchool(rs.getString("school"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return account;
	}

	public static Account selectById(int id) {
		Account account = null;
		ResultSet rs = executeQuery("SELECT * FROM member WHERE id=?", id + "");
		try {
			if (rs.next()) {
				account = new Account(rs.getInt("id"),
						rs.getString("username"), rs.getString("displayname"),
						rs.getString("type"), rs.getInt("m_state"));
				account.setAvatar(rs.getString("avatar"));
				account.setBirthday(rs.getDate("birthday"));
				account.setJoin(rs.getDate("join"));
				account.setAddresss(rs.getString("address"));
				account.setJob(rs.getString("job"));
				account.setEmail(rs.getString("email"));
				account.setSex(rs.getString("sex"));
				account.setSchool(rs.getString("school"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return account;
	}

	public static Account selectByUsername(String username) {
		Account account = null;
		ResultSet rs = executeQuery("SELECT * FROM member WHERE username=?",
				username);
		try {
			if (rs.next()) {
				account = new Account(rs.getInt("id"),
						rs.getString("username"), rs.getString("displayname"),
						rs.getString("type"), rs.getInt("m_state"));
				account.setAvatar(rs.getString("avatar"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return account;
	}

	public static String selectPass(int id) {
		ResultSet rs = executeQuery("SELECT password FROM member WHERE id=?",
				id + "");
		try {
			if (rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * change password of account
	 * 
	 * @param id
	 * @param currentPass
	 * @param newPass
	 * @return -1 if has error, 0 if successful, 1 if password is not match, 2
	 *         if error sql
	 */
	public static byte changePassword(int id, String currentPass, String newPass) {
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT member.password=? FROM member WHERE id=?");
			rs = executeQuery(pre, currentPass, id + "");

			if (rs.next()) {
				if (!rs.getBoolean(1)) {
					return 1; // password not valid
				} else {
					pre = conn
							.prepareStatement("UPDATE member SET password=? WHERE id=?");
					pre.setString(1, newPass);
					pre.setInt(2, id);

					int row = pre.executeUpdate();
					if (row > 0) {
						return 0; // successful
					} else {
						return 2; // fail
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pre.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1; // err processing
	}

	public static boolean changeInfor(Account account) {
		boolean result = false;
		if (account != null) {
			Connection conn = null;
			PreparedStatement pre = null;

			try {
				conn = Database.connect();
				pre = conn
						.prepareStatement("UPDATE member SET displayname=?,sex=?,birthday=?,email=?,job=?,school=?,address=? WHERE id=?");
				pre.setString(1, account.getDisplayName());
				pre.setString(2, account.getSex());
				pre.setDate(3, (Date) account.getBirthday());
				pre.setString(4, account.getEmail());
				pre.setString(5, account.getJob());
				pre.setString(6, account.getSchoolName());
				pre.setString(7, account.getAddress());
				pre.setInt(8, account.getId());

				int rows = pre.executeUpdate();

				if (rows > 0)
					result = true;
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					pre.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static ResultSet executeQuery(PreparedStatement pre,
			String... params) throws SQLException {
		for (int i = 0; i < params.length; i++) {
			pre.setString(i + 1, params[i]);
		}
		return pre.executeQuery();
	}

	public static List<Account> selectAll() {
		List<Account> list = new ArrayList<Account>();
		ResultSet rs = executeQuery("SELECT * FROM member");

		try {
			while (rs.next()) {
				Account acc = new Account(rs.getInt("id"),
						rs.getString("username"), rs.getString("displayname"),
						rs.getString("type"), rs.getInt("m_state"));
				acc.setJoin(rs.getDate("join"));
				list.add(acc);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	private static ResultSet executeQuery(String query, String... param) {
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement(query);
			for (int i = 0; i < param.length; i++) {
				pre.setString(i + 1, param[i]);
			}
			rs = pre.executeQuery();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// try {
			// pre.close();
			// conn.close();
			// } catch (SQLException e) {
			// e.printStackTrace();
			// }
		}
		return rs;
	}

	public static void main(String[] args) throws ParseException {
		System.out.println(selectAll().size());
	}

	public static boolean changeAvatar(String imgName, String id) {
		Connection conn = null;
		PreparedStatement pre = null;
		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("UPDATE member SET avatar=? WHERE id=?");
			pre.setString(1, imgName);
			pre.setString(2, id);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ReflectiveOperationException e) {
			e.printStackTrace();
		} finally {
			try {
				pre.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	public static boolean deleteById(String id) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement("DELETE FROM member WHERE id=?");
			pre.setString(1, id);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static boolean insert(String... str) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("INSERT INTO member(username, displayname, password, sex, birthday) VALUES(?,?,?,?,?)");
			for (int i = 0; i < str.length; i++) {
				if (i == str.length - 1) {
					pre.setDate(i + 1, java.sql.Date.valueOf(str[i]));
				} else
					pre.setString(i + 1, str[i]);
			}

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static boolean updatePosts(int id) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("UPDATE member SET posts=posts+1 WHERE id=?");
			pre.setInt(1, id);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
